var express = require('express');
var router = express.Router();
var sqlite3 = require('sqlite3').verbose();
var path = require('path');
var fs = require('fs');
/* GET users listing. */
router.get('/', function(req, res, next) {
    var db=new sqlite3.Database(path.join(__dirname, "../db/data.s3db"));
    if(req.query.source=="table_para"){
        var id =req.query.id;
        var course = req.query.course;
        sqlStr = "SELECT * from specification where id = ? and course = ?";//查询指定数量的记录
        db.all(sqlStr,[id,course],function(err, rows){
            fs.readFile('../code/public/api/mini_table.json', 'utf8', function (err, data) {
                if (err) console.log(err);
                var table = JSON.parse(data);
                table.data = rows;
                table.count = rows.length;
                var t = JSON.stringify(table);
                fs.writeFileSync('../code/public/api/mini_table.json', t)
            });
        } );
    }else if(req.query.function =="add"){
        var id = req.query.data.userid;
        var name = req.query.data.username;
        var sex = req.query.data.sex;
        var course = req.query.data.course;
        var class_ = req.query.data.class;
        db.run("INSERT INTO student VALUES(?,?,?,?,?,?)",id,name,sex,course,class_,"0",function(err){
            if(err==null){
                res.send("1");
            }else{
                res.send("0");
            }
        });
    }else if(req.query.function =="edit"){//修改信息
        var userid = req.query.id;
        if(req.query.data.username!=""){
            db.run("UPDATE student SET name = ? WHERE id = ?",req.query.data.username,userid,function(err){
                if(err!=null)
                    res.send('0');
            })
        }
        if(req.query.data.class!=""){
            db.run("UPDATE student SET class = ? WHERE id = ?",req.query.data.class,userid,function(err){
                if(err!=null)
                    res.send('0');
            })
        }
        if(req.query.data.course!=""){
            db.run("UPDATE student SET course = ? WHERE id = ?",req.query.data.course,userid,function(err){
                if(err!=null)
                    res.send('0');
            })
        }
    }else if(req.query.function =="delete"){
        var id_ = req.query.id;
        db.run("DELETE FROM student  WHERE id = ?",id_,function(err){
            if(err!=null)
                res.send('0');
        })
    }else if(req.query.function == "chart"){
        sqlStr = "SELECT week , count( * ) num FROM specification WHERE week IN ( SELECT week FROM specification GROUP BY week ) GROUP BY week ;";//查询指定数量的记录
        db.all(sqlStr,function(err, rows){
            if(rows.length==0){
                res.send('0');
            }else{
                res.send(JSON.stringify(rows));
            }
        })
    }
    else if(req.query.function = "delete_many"){
        for(var i = 0 ;i < req.query.data.length ;i++){
            var id_ = req.query.data[i].id;
            db.run("DELETE FROM student  WHERE id = ?",id_,function(err){
                if(err!=null)
                    res.send('0');
            })
        }
    }
    // db.all("SELECT * from student",function(err, rows){
    //     if(rows.length==0){
    //         res.send("0");
    //     }else{
    //         len = rows.length;
    //     }
    // } );

});

module.exports = router;
